{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "67dfcc89-34ff-400b-b71a-f3067000b8f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np \n",
    "from sklearn.impute import SimpleImputer\n",
    "from sklearn.preprocessing import StandardScaler\n",
    "from sklearn.preprocessing import OneHotEncoder\n",
    "from sklearn.compose import ColumnTransformer\n",
    "from sklearn.decomposition import PCA\n",
    "from sklearn.discriminant_analysis import LinearDiscriminantAnalysis\n",
    "from imblearn.pipeline import Pipeline\n",
    "from imblearn.over_sampling import SMOTE\n",
    "from imblearn.under_sampling import RandomUnderSampler \n",
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.feature_selection import SelectFromModel\n",
    "from sklearn.ensemble import RandomForestClassifier\n",
    "from sklearn.metrics import roc_auc_score\n",
    "from xgboost import XGBClassifier as xgb\n",
    "from catboost import CatBoostClassifier\n",
    "from sklearn.model_selection import cross_validate\n",
    "from sklearn.model_selection import RepeatedStratifiedKFold\n",
    "from sklearn.model_selection import RandomizedSearchCV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "d4e9451d-4b36-45ef-b62d-b3f31963b216",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_ID</th>\n",
       "      <th>S_2</th>\n",
       "      <th>P_2</th>\n",
       "      <th>D_39</th>\n",
       "      <th>B_1</th>\n",
       "      <th>B_2</th>\n",
       "      <th>R_1</th>\n",
       "      <th>S_3</th>\n",
       "      <th>D_41</th>\n",
       "      <th>B_3</th>\n",
       "      <th>...</th>\n",
       "      <th>D_136</th>\n",
       "      <th>D_137</th>\n",
       "      <th>D_138</th>\n",
       "      <th>D_139</th>\n",
       "      <th>D_140</th>\n",
       "      <th>D_141</th>\n",
       "      <th>D_142</th>\n",
       "      <th>D_143</th>\n",
       "      <th>D_144</th>\n",
       "      <th>D_145</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0000099d6bd597052cdcda90ffabf56573fe9d7c79be5f...</td>\n",
       "      <td>2017-03-09</td>\n",
       "      <td>0.938469</td>\n",
       "      <td>0.001733</td>\n",
       "      <td>0.008724</td>\n",
       "      <td>1.006838</td>\n",
       "      <td>0.009228</td>\n",
       "      <td>0.124035</td>\n",
       "      <td>0.008771</td>\n",
       "      <td>0.004709</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.002427</td>\n",
       "      <td>0.003706</td>\n",
       "      <td>0.003818</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000569</td>\n",
       "      <td>0.000610</td>\n",
       "      <td>0.002674</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0000099d6bd597052cdcda90ffabf56573fe9d7c79be5f...</td>\n",
       "      <td>2017-04-07</td>\n",
       "      <td>0.936665</td>\n",
       "      <td>0.005775</td>\n",
       "      <td>0.004923</td>\n",
       "      <td>1.000653</td>\n",
       "      <td>0.006151</td>\n",
       "      <td>0.126750</td>\n",
       "      <td>0.000798</td>\n",
       "      <td>0.002714</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.003954</td>\n",
       "      <td>0.003167</td>\n",
       "      <td>0.005032</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.009576</td>\n",
       "      <td>0.005492</td>\n",
       "      <td>0.009217</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0000099d6bd597052cdcda90ffabf56573fe9d7c79be5f...</td>\n",
       "      <td>2017-05-28</td>\n",
       "      <td>0.954180</td>\n",
       "      <td>0.091505</td>\n",
       "      <td>0.021655</td>\n",
       "      <td>1.009672</td>\n",
       "      <td>0.006815</td>\n",
       "      <td>0.123977</td>\n",
       "      <td>0.007598</td>\n",
       "      <td>0.009423</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.003269</td>\n",
       "      <td>0.007329</td>\n",
       "      <td>0.000427</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.003429</td>\n",
       "      <td>0.006986</td>\n",
       "      <td>0.002603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0000099d6bd597052cdcda90ffabf56573fe9d7c79be5f...</td>\n",
       "      <td>2017-06-13</td>\n",
       "      <td>0.960384</td>\n",
       "      <td>0.002455</td>\n",
       "      <td>0.013683</td>\n",
       "      <td>1.002700</td>\n",
       "      <td>0.001373</td>\n",
       "      <td>0.117169</td>\n",
       "      <td>0.000685</td>\n",
       "      <td>0.005531</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.006117</td>\n",
       "      <td>0.004516</td>\n",
       "      <td>0.003200</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.008419</td>\n",
       "      <td>0.006527</td>\n",
       "      <td>0.009600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0000099d6bd597052cdcda90ffabf56573fe9d7c79be5f...</td>\n",
       "      <td>2017-07-16</td>\n",
       "      <td>0.947248</td>\n",
       "      <td>0.002483</td>\n",
       "      <td>0.015193</td>\n",
       "      <td>1.000727</td>\n",
       "      <td>0.007605</td>\n",
       "      <td>0.117325</td>\n",
       "      <td>0.004653</td>\n",
       "      <td>0.009312</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.003671</td>\n",
       "      <td>0.004946</td>\n",
       "      <td>0.008889</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.001670</td>\n",
       "      <td>0.008126</td>\n",
       "      <td>0.009827</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79995</th>\n",
       "      <td>03b52bb30a6a13794a48eeaee1de91b3fe06b24d0ce1fe...</td>\n",
       "      <td>2018-01-04</td>\n",
       "      <td>0.226898</td>\n",
       "      <td>0.589775</td>\n",
       "      <td>0.487723</td>\n",
       "      <td>0.021488</td>\n",
       "      <td>1.004159</td>\n",
       "      <td>0.146009</td>\n",
       "      <td>0.003498</td>\n",
       "      <td>0.845987</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.002641</td>\n",
       "      <td>0.007004</td>\n",
       "      <td>0.006104</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.007694</td>\n",
       "      <td>0.006791</td>\n",
       "      <td>0.007444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79996</th>\n",
       "      <td>03b52bb30a6a13794a48eeaee1de91b3fe06b24d0ce1fe...</td>\n",
       "      <td>2018-02-02</td>\n",
       "      <td>0.262578</td>\n",
       "      <td>0.005605</td>\n",
       "      <td>0.481387</td>\n",
       "      <td>0.020650</td>\n",
       "      <td>1.004511</td>\n",
       "      <td>0.176715</td>\n",
       "      <td>0.000653</td>\n",
       "      <td>0.964764</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.002716</td>\n",
       "      <td>0.002369</td>\n",
       "      <td>0.001726</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.004508</td>\n",
       "      <td>0.000412</td>\n",
       "      <td>0.002581</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79997</th>\n",
       "      <td>03b52bb30a6a13794a48eeaee1de91b3fe06b24d0ce1fe...</td>\n",
       "      <td>2018-03-28</td>\n",
       "      <td>0.031075</td>\n",
       "      <td>0.362148</td>\n",
       "      <td>0.314964</td>\n",
       "      <td>0.022732</td>\n",
       "      <td>1.508125</td>\n",
       "      <td>0.189738</td>\n",
       "      <td>0.001785</td>\n",
       "      <td>0.899987</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.002859</td>\n",
       "      <td>0.009245</td>\n",
       "      <td>0.001701</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.008676</td>\n",
       "      <td>0.005630</td>\n",
       "      <td>0.003382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79998</th>\n",
       "      <td>03b52c49b3f8ea1cb831d9804affc3b5e453df140e47e1...</td>\n",
       "      <td>2017-03-29</td>\n",
       "      <td>0.541041</td>\n",
       "      <td>0.001059</td>\n",
       "      <td>0.016899</td>\n",
       "      <td>0.817418</td>\n",
       "      <td>0.001329</td>\n",
       "      <td>0.369386</td>\n",
       "      <td>0.000889</td>\n",
       "      <td>0.011950</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.001559</td>\n",
       "      <td>0.007480</td>\n",
       "      <td>0.931257</td>\n",
       "      <td>0.346410</td>\n",
       "      <td>1.001893</td>\n",
       "      <td>0.222562</td>\n",
       "      <td>0.096550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79999</th>\n",
       "      <td>03b52c49b3f8ea1cb831d9804affc3b5e453df140e47e1...</td>\n",
       "      <td>2017-04-11</td>\n",
       "      <td>0.540710</td>\n",
       "      <td>0.009107</td>\n",
       "      <td>0.015722</td>\n",
       "      <td>0.812307</td>\n",
       "      <td>0.008008</td>\n",
       "      <td>0.361599</td>\n",
       "      <td>0.007559</td>\n",
       "      <td>0.006491</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.002673</td>\n",
       "      <td>0.000989</td>\n",
       "      <td>0.940726</td>\n",
       "      <td>0.355327</td>\n",
       "      <td>1.005775</td>\n",
       "      <td>0.225669</td>\n",
       "      <td>0.093913</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>80000 rows × 190 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             customer_ID         S_2  \\\n",
       "0      0000099d6bd597052cdcda90ffabf56573fe9d7c79be5f...  2017-03-09   \n",
       "1      0000099d6bd597052cdcda90ffabf56573fe9d7c79be5f...  2017-04-07   \n",
       "2      0000099d6bd597052cdcda90ffabf56573fe9d7c79be5f...  2017-05-28   \n",
       "3      0000099d6bd597052cdcda90ffabf56573fe9d7c79be5f...  2017-06-13   \n",
       "4      0000099d6bd597052cdcda90ffabf56573fe9d7c79be5f...  2017-07-16   \n",
       "...                                                  ...         ...   \n",
       "79995  03b52bb30a6a13794a48eeaee1de91b3fe06b24d0ce1fe...  2018-01-04   \n",
       "79996  03b52bb30a6a13794a48eeaee1de91b3fe06b24d0ce1fe...  2018-02-02   \n",
       "79997  03b52bb30a6a13794a48eeaee1de91b3fe06b24d0ce1fe...  2018-03-28   \n",
       "79998  03b52c49b3f8ea1cb831d9804affc3b5e453df140e47e1...  2017-03-29   \n",
       "79999  03b52c49b3f8ea1cb831d9804affc3b5e453df140e47e1...  2017-04-11   \n",
       "\n",
       "            P_2      D_39       B_1       B_2       R_1       S_3      D_41  \\\n",
       "0      0.938469  0.001733  0.008724  1.006838  0.009228  0.124035  0.008771   \n",
       "1      0.936665  0.005775  0.004923  1.000653  0.006151  0.126750  0.000798   \n",
       "2      0.954180  0.091505  0.021655  1.009672  0.006815  0.123977  0.007598   \n",
       "3      0.960384  0.002455  0.013683  1.002700  0.001373  0.117169  0.000685   \n",
       "4      0.947248  0.002483  0.015193  1.000727  0.007605  0.117325  0.004653   \n",
       "...         ...       ...       ...       ...       ...       ...       ...   \n",
       "79995  0.226898  0.589775  0.487723  0.021488  1.004159  0.146009  0.003498   \n",
       "79996  0.262578  0.005605  0.481387  0.020650  1.004511  0.176715  0.000653   \n",
       "79997  0.031075  0.362148  0.314964  0.022732  1.508125  0.189738  0.001785   \n",
       "79998  0.541041  0.001059  0.016899  0.817418  0.001329  0.369386  0.000889   \n",
       "79999  0.540710  0.009107  0.015722  0.812307  0.008008  0.361599  0.007559   \n",
       "\n",
       "            B_3  ...  D_136  D_137  D_138     D_139     D_140     D_141  \\\n",
       "0      0.004709  ...    NaN    NaN    NaN  0.002427  0.003706  0.003818   \n",
       "1      0.002714  ...    NaN    NaN    NaN  0.003954  0.003167  0.005032   \n",
       "2      0.009423  ...    NaN    NaN    NaN  0.003269  0.007329  0.000427   \n",
       "3      0.005531  ...    NaN    NaN    NaN  0.006117  0.004516  0.003200   \n",
       "4      0.009312  ...    NaN    NaN    NaN  0.003671  0.004946  0.008889   \n",
       "...         ...  ...    ...    ...    ...       ...       ...       ...   \n",
       "79995  0.845987  ...    NaN    NaN    NaN  0.002641  0.007004  0.006104   \n",
       "79996  0.964764  ...    NaN    NaN    NaN  0.002716  0.002369  0.001726   \n",
       "79997  0.899987  ...    NaN    NaN    NaN  0.002859  0.009245  0.001701   \n",
       "79998  0.011950  ...    NaN    NaN    NaN  1.001559  0.007480  0.931257   \n",
       "79999  0.006491  ...    NaN    NaN    NaN  1.002673  0.000989  0.940726   \n",
       "\n",
       "          D_142     D_143     D_144     D_145  \n",
       "0           NaN  0.000569  0.000610  0.002674  \n",
       "1           NaN  0.009576  0.005492  0.009217  \n",
       "2           NaN  0.003429  0.006986  0.002603  \n",
       "3           NaN  0.008419  0.006527  0.009600  \n",
       "4           NaN  0.001670  0.008126  0.009827  \n",
       "...         ...       ...       ...       ...  \n",
       "79995       NaN  0.007694  0.006791  0.007444  \n",
       "79996       NaN  0.004508  0.000412  0.002581  \n",
       "79997       NaN  0.008676  0.005630  0.003382  \n",
       "79998  0.346410  1.001893  0.222562  0.096550  \n",
       "79999  0.355327  1.005775  0.225669  0.093913  \n",
       "\n",
       "[80000 rows x 190 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df_sample = pd.read_csv('data/train_data.csv', nrows=80000)\n",
    "train_df_sample"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b5a38cd5-4c42-4ccb-84f6-533022402aca",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_ID</th>\n",
       "      <th>target</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0000099d6bd597052cdcda90ffabf56573fe9d7c79be5f...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00000fd6641609c6ece5454664794f0340ad84dddce9a2...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00001b22f846c82c51f6e3958ccd81970162bae8b007e8...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000041bdba6ecadd89a52d11886e8eaaec9325906c9723...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00007889e4fcd2614b6cbe7f8f3d2e5c728eca32d9eb8a...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>458908</th>\n",
       "      <td>ffff41c8a52833b56430603969b9ca48d208e7c192c6a4...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>458909</th>\n",
       "      <td>ffff518bb2075e4816ee3fe9f3b152c57fc0e6f01bf7fd...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>458910</th>\n",
       "      <td>ffff9984b999fccb2b6127635ed0736dda94e544e67e02...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>458911</th>\n",
       "      <td>ffffa5c46bc8de74f5a4554e74e239c8dee6b9baf38814...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>458912</th>\n",
       "      <td>fffff1d38b785cef84adeace64f8f83db3a0c31e8d92ea...</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>458913 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              customer_ID  target\n",
       "0       0000099d6bd597052cdcda90ffabf56573fe9d7c79be5f...       0\n",
       "1       00000fd6641609c6ece5454664794f0340ad84dddce9a2...       0\n",
       "2       00001b22f846c82c51f6e3958ccd81970162bae8b007e8...       0\n",
       "3       000041bdba6ecadd89a52d11886e8eaaec9325906c9723...       0\n",
       "4       00007889e4fcd2614b6cbe7f8f3d2e5c728eca32d9eb8a...       0\n",
       "...                                                   ...     ...\n",
       "458908  ffff41c8a52833b56430603969b9ca48d208e7c192c6a4...       0\n",
       "458909  ffff518bb2075e4816ee3fe9f3b152c57fc0e6f01bf7fd...       0\n",
       "458910  ffff9984b999fccb2b6127635ed0736dda94e544e67e02...       0\n",
       "458911  ffffa5c46bc8de74f5a4554e74e239c8dee6b9baf38814...       1\n",
       "458912  fffff1d38b785cef84adeace64f8f83db3a0c31e8d92ea...       0\n",
       "\n",
       "[458913 rows x 2 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_label_df = pd.read_csv('data/train_labels.csv')\n",
    "train_label_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "6cd44fad-a48f-4eeb-b996-74a0631274e9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>S_2</th>\n",
       "      <th>P_2</th>\n",
       "      <th>D_39</th>\n",
       "      <th>B_1</th>\n",
       "      <th>B_2</th>\n",
       "      <th>R_1</th>\n",
       "      <th>S_3</th>\n",
       "      <th>D_41</th>\n",
       "      <th>B_3</th>\n",
       "      <th>D_42</th>\n",
       "      <th>...</th>\n",
       "      <th>D_136</th>\n",
       "      <th>D_137</th>\n",
       "      <th>D_138</th>\n",
       "      <th>D_139</th>\n",
       "      <th>D_140</th>\n",
       "      <th>D_141</th>\n",
       "      <th>D_142</th>\n",
       "      <th>D_143</th>\n",
       "      <th>D_144</th>\n",
       "      <th>D_145</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>customer_ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>00000469ba478561f23a92a868bd366de6f6527a684c9a2e78fb826dcac3b9b7</th>\n",
       "      <td>2019-02-19</td>\n",
       "      <td>0.631315</td>\n",
       "      <td>0.001912</td>\n",
       "      <td>0.010728</td>\n",
       "      <td>0.814497</td>\n",
       "      <td>0.007547</td>\n",
       "      <td>0.168651</td>\n",
       "      <td>0.009971</td>\n",
       "      <td>0.002347</td>\n",
       "      <td>0.113189</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.004669</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.008281</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00000469ba478561f23a92a868bd366de6f6527a684c9a2e78fb826dcac3b9b7</th>\n",
       "      <td>2019-03-25</td>\n",
       "      <td>0.587042</td>\n",
       "      <td>0.005275</td>\n",
       "      <td>0.011026</td>\n",
       "      <td>0.810848</td>\n",
       "      <td>0.001817</td>\n",
       "      <td>0.241389</td>\n",
       "      <td>0.000166</td>\n",
       "      <td>0.009132</td>\n",
       "      <td>0.123035</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000142</td>\n",
       "      <td>0.004940</td>\n",
       "      <td>0.009021</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.003695</td>\n",
       "      <td>0.003753</td>\n",
       "      <td>0.001460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00000469ba478561f23a92a868bd366de6f6527a684c9a2e78fb826dcac3b9b7</th>\n",
       "      <td>2019-04-25</td>\n",
       "      <td>0.609056</td>\n",
       "      <td>0.003326</td>\n",
       "      <td>0.016390</td>\n",
       "      <td>1.004620</td>\n",
       "      <td>0.000114</td>\n",
       "      <td>0.266976</td>\n",
       "      <td>0.004196</td>\n",
       "      <td>0.004192</td>\n",
       "      <td>0.125319</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000074</td>\n",
       "      <td>0.002114</td>\n",
       "      <td>0.004656</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.003155</td>\n",
       "      <td>0.002156</td>\n",
       "      <td>0.006482</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00000469ba478561f23a92a868bd366de6f6527a684c9a2e78fb826dcac3b9b7</th>\n",
       "      <td>2019-05-20</td>\n",
       "      <td>0.614911</td>\n",
       "      <td>0.009065</td>\n",
       "      <td>0.021672</td>\n",
       "      <td>0.816549</td>\n",
       "      <td>0.009722</td>\n",
       "      <td>0.188947</td>\n",
       "      <td>0.004123</td>\n",
       "      <td>0.015325</td>\n",
       "      <td>0.123439</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.004743</td>\n",
       "      <td>0.006392</td>\n",
       "      <td>0.002890</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.006044</td>\n",
       "      <td>0.005206</td>\n",
       "      <td>0.007855</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00000469ba478561f23a92a868bd366de6f6527a684c9a2e78fb826dcac3b9b7</th>\n",
       "      <td>2019-06-15</td>\n",
       "      <td>0.591673</td>\n",
       "      <td>0.238794</td>\n",
       "      <td>0.015923</td>\n",
       "      <td>0.810456</td>\n",
       "      <td>0.002026</td>\n",
       "      <td>0.180035</td>\n",
       "      <td>0.000731</td>\n",
       "      <td>0.011281</td>\n",
       "      <td>0.122212</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.008133</td>\n",
       "      <td>0.004329</td>\n",
       "      <td>0.008384</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.001008</td>\n",
       "      <td>0.007421</td>\n",
       "      <td>0.009471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873bea8bed0312342f02</th>\n",
       "      <td>2019-02-09</td>\n",
       "      <td>0.683701</td>\n",
       "      <td>0.001278</td>\n",
       "      <td>0.021802</td>\n",
       "      <td>1.000436</td>\n",
       "      <td>0.002883</td>\n",
       "      <td>0.119966</td>\n",
       "      <td>0.009982</td>\n",
       "      <td>0.032577</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.008077</td>\n",
       "      <td>0.000589</td>\n",
       "      <td>0.009818</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.004587</td>\n",
       "      <td>0.003140</td>\n",
       "      <td>0.001961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873bea8bed0312342f02</th>\n",
       "      <td>2019-03-16</td>\n",
       "      <td>0.644960</td>\n",
       "      <td>0.009178</td>\n",
       "      <td>0.000942</td>\n",
       "      <td>0.815873</td>\n",
       "      <td>0.005297</td>\n",
       "      <td>0.158137</td>\n",
       "      <td>0.003946</td>\n",
       "      <td>0.025303</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.006871</td>\n",
       "      <td>0.005859</td>\n",
       "      <td>0.001584</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.004597</td>\n",
       "      <td>0.009147</td>\n",
       "      <td>0.009150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873bea8bed0312342f02</th>\n",
       "      <td>2019-04-16</td>\n",
       "      <td>0.699122</td>\n",
       "      <td>0.002243</td>\n",
       "      <td>0.009730</td>\n",
       "      <td>1.008919</td>\n",
       "      <td>0.004538</td>\n",
       "      <td>0.157636</td>\n",
       "      <td>0.004611</td>\n",
       "      <td>0.021294</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.003082</td>\n",
       "      <td>0.004420</td>\n",
       "      <td>0.007995</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.001494</td>\n",
       "      <td>0.007972</td>\n",
       "      <td>0.008576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873bea8bed0312342f02</th>\n",
       "      <td>2019-05-25</td>\n",
       "      <td>0.581679</td>\n",
       "      <td>0.008107</td>\n",
       "      <td>0.003331</td>\n",
       "      <td>0.814270</td>\n",
       "      <td>0.006477</td>\n",
       "      <td>0.142948</td>\n",
       "      <td>0.009730</td>\n",
       "      <td>0.012458</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.007863</td>\n",
       "      <td>0.008646</td>\n",
       "      <td>0.009229</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.007407</td>\n",
       "      <td>0.002618</td>\n",
       "      <td>0.008412</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873bea8bed0312342f02</th>\n",
       "      <td>2019-06-15</td>\n",
       "      <td>0.582570</td>\n",
       "      <td>0.008559</td>\n",
       "      <td>0.031728</td>\n",
       "      <td>0.814603</td>\n",
       "      <td>0.007265</td>\n",
       "      <td>0.112929</td>\n",
       "      <td>0.001307</td>\n",
       "      <td>0.002302</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.008963</td>\n",
       "      <td>0.005623</td>\n",
       "      <td>0.001422</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.005165</td>\n",
       "      <td>0.004160</td>\n",
       "      <td>0.005322</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>80000 rows × 189 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                           S_2       P_2  \\\n",
       "customer_ID                                                                \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  2019-02-19  0.631315   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  2019-03-25  0.587042   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  2019-04-25  0.609056   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  2019-05-20  0.614911   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  2019-06-15  0.591673   \n",
       "...                                                        ...       ...   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  2019-02-09  0.683701   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  2019-03-16  0.644960   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  2019-04-16  0.699122   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  2019-05-25  0.581679   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  2019-06-15  0.582570   \n",
       "\n",
       "                                                        D_39       B_1  \\\n",
       "customer_ID                                                              \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.001912  0.010728   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.005275  0.011026   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.003326  0.016390   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.009065  0.021672   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.238794  0.015923   \n",
       "...                                                      ...       ...   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.001278  0.021802   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.009178  0.000942   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.002243  0.009730   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.008107  0.003331   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.008559  0.031728   \n",
       "\n",
       "                                                         B_2       R_1  \\\n",
       "customer_ID                                                              \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.814497  0.007547   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.810848  0.001817   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  1.004620  0.000114   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.816549  0.009722   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.810456  0.002026   \n",
       "...                                                      ...       ...   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  1.000436  0.002883   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.815873  0.005297   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  1.008919  0.004538   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.814270  0.006477   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.814603  0.007265   \n",
       "\n",
       "                                                         S_3      D_41  \\\n",
       "customer_ID                                                              \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.168651  0.009971   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.241389  0.000166   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.266976  0.004196   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.188947  0.004123   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.180035  0.000731   \n",
       "...                                                      ...       ...   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.119966  0.009982   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.158137  0.003946   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.157636  0.004611   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.142948  0.009730   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.112929  0.001307   \n",
       "\n",
       "                                                         B_3      D_42  ...  \\\n",
       "customer_ID                                                             ...   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.002347  0.113189  ...   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.009132  0.123035  ...   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.004192  0.125319  ...   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.015325  0.123439  ...   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.011281  0.122212  ...   \n",
       "...                                                      ...       ...  ...   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.032577       NaN  ...   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.025303       NaN  ...   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.021294       NaN  ...   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.012458       NaN  ...   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.002302       NaN  ...   \n",
       "\n",
       "                                                    D_136  D_137  D_138  \\\n",
       "customer_ID                                                               \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...    NaN    NaN    NaN   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...    NaN    NaN    NaN   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...    NaN    NaN    NaN   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...    NaN    NaN    NaN   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...    NaN    NaN    NaN   \n",
       "...                                                   ...    ...    ...   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...    NaN    NaN    NaN   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...    NaN    NaN    NaN   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...    NaN    NaN    NaN   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...    NaN    NaN    NaN   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...    NaN    NaN    NaN   \n",
       "\n",
       "                                                       D_139     D_140  \\\n",
       "customer_ID                                                              \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...       NaN  0.004669   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.000142  0.004940   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.000074  0.002114   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.004743  0.006392   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.008133  0.004329   \n",
       "...                                                      ...       ...   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.008077  0.000589   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.006871  0.005859   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.003082  0.004420   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.007863  0.008646   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.008963  0.005623   \n",
       "\n",
       "                                                       D_141  D_142     D_143  \\\n",
       "customer_ID                                                                     \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...       NaN    NaN       NaN   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.009021    NaN  0.003695   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.004656    NaN  0.003155   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.002890    NaN  0.006044   \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.008384    NaN  0.001008   \n",
       "...                                                      ...    ...       ...   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.009818    NaN  0.004587   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.001584    NaN  0.004597   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.007995    NaN  0.001494   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.009229    NaN  0.007407   \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.001422    NaN  0.005165   \n",
       "\n",
       "                                                       D_144     D_145  \n",
       "customer_ID                                                             \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.008281       NaN  \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.003753  0.001460  \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.002156  0.006482  \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.005206  0.007855  \n",
       "00000469ba478561f23a92a868bd366de6f6527a684c9a2...  0.007421  0.009471  \n",
       "...                                                      ...       ...  \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.003140  0.001961  \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.009147  0.009150  \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.007972  0.008576  \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.002618  0.008412  \n",
       "01d4a24ebb862aee77fce5bf808f98d89ab56ab4974c873...  0.004160  0.005322  \n",
       "\n",
       "[80000 rows x 189 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_df = pd.read_csv('data/test_data.csv', nrows=80000, index_col='customer_ID')\n",
    "test_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "4b9d4eb1-092c-4c48-8078-8784a411150a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<bound method DataFrame.info of                                              customer_ID         S_2  \\\n",
       "0      0000099d6bd597052cdcda90ffabf56573fe9d7c79be5f...  2017-03-09   \n",
       "1      0000099d6bd597052cdcda90ffabf56573fe9d7c79be5f...  2017-04-07   \n",
       "2      0000099d6bd597052cdcda90ffabf56573fe9d7c79be5f...  2017-05-28   \n",
       "3      0000099d6bd597052cdcda90ffabf56573fe9d7c79be5f...  2017-06-13   \n",
       "4      0000099d6bd597052cdcda90ffabf56573fe9d7c79be5f...  2017-07-16   \n",
       "...                                                  ...         ...   \n",
       "79995  03b52bb30a6a13794a48eeaee1de91b3fe06b24d0ce1fe...  2018-01-04   \n",
       "79996  03b52bb30a6a13794a48eeaee1de91b3fe06b24d0ce1fe...  2018-02-02   \n",
       "79997  03b52bb30a6a13794a48eeaee1de91b3fe06b24d0ce1fe...  2018-03-28   \n",
       "79998  03b52c49b3f8ea1cb831d9804affc3b5e453df140e47e1...  2017-03-29   \n",
       "79999  03b52c49b3f8ea1cb831d9804affc3b5e453df140e47e1...  2017-04-11   \n",
       "\n",
       "            P_2      D_39       B_1       B_2       R_1       S_3      D_41  \\\n",
       "0      0.938469  0.001733  0.008724  1.006838  0.009228  0.124035  0.008771   \n",
       "1      0.936665  0.005775  0.004923  1.000653  0.006151  0.126750  0.000798   \n",
       "2      0.954180  0.091505  0.021655  1.009672  0.006815  0.123977  0.007598   \n",
       "3      0.960384  0.002455  0.013683  1.002700  0.001373  0.117169  0.000685   \n",
       "4      0.947248  0.002483  0.015193  1.000727  0.007605  0.117325  0.004653   \n",
       "...         ...       ...       ...       ...       ...       ...       ...   \n",
       "79995  0.226898  0.589775  0.487723  0.021488  1.004159  0.146009  0.003498   \n",
       "79996  0.262578  0.005605  0.481387  0.020650  1.004511  0.176715  0.000653   \n",
       "79997  0.031075  0.362148  0.314964  0.022732  1.508125  0.189738  0.001785   \n",
       "79998  0.541041  0.001059  0.016899  0.817418  0.001329  0.369386  0.000889   \n",
       "79999  0.540710  0.009107  0.015722  0.812307  0.008008  0.361599  0.007559   \n",
       "\n",
       "            B_3  ...  D_137  D_138     D_139     D_140     D_141     D_142  \\\n",
       "0      0.004709  ...    NaN    NaN  0.002427  0.003706  0.003818       NaN   \n",
       "1      0.002714  ...    NaN    NaN  0.003954  0.003167  0.005032       NaN   \n",
       "2      0.009423  ...    NaN    NaN  0.003269  0.007329  0.000427       NaN   \n",
       "3      0.005531  ...    NaN    NaN  0.006117  0.004516  0.003200       NaN   \n",
       "4      0.009312  ...    NaN    NaN  0.003671  0.004946  0.008889       NaN   \n",
       "...         ...  ...    ...    ...       ...       ...       ...       ...   \n",
       "79995  0.845987  ...    NaN    NaN  0.002641  0.007004  0.006104       NaN   \n",
       "79996  0.964764  ...    NaN    NaN  0.002716  0.002369  0.001726       NaN   \n",
       "79997  0.899987  ...    NaN    NaN  0.002859  0.009245  0.001701       NaN   \n",
       "79998  0.011950  ...    NaN    NaN  1.001559  0.007480  0.931257  0.346410   \n",
       "79999  0.006491  ...    NaN    NaN  1.002673  0.000989  0.940726  0.355327   \n",
       "\n",
       "          D_143     D_144     D_145  target  \n",
       "0      0.000569  0.000610  0.002674       0  \n",
       "1      0.009576  0.005492  0.009217       0  \n",
       "2      0.003429  0.006986  0.002603       0  \n",
       "3      0.008419  0.006527  0.009600       0  \n",
       "4      0.001670  0.008126  0.009827       0  \n",
       "...         ...       ...       ...     ...  \n",
       "79995  0.007694  0.006791  0.007444       1  \n",
       "79996  0.004508  0.000412  0.002581       1  \n",
       "79997  0.008676  0.005630  0.003382       1  \n",
       "79998  1.001893  0.222562  0.096550       0  \n",
       "79999  1.005775  0.225669  0.093913       0  \n",
       "\n",
       "[80000 rows x 191 columns]>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df = pd.merge(train_df_sample,train_label_df,how=\"inner\",on=[\"customer_ID\"])\n",
    "train_df.info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "bf3f7005-68b3-4cc0-8357-588e37cd3bd7",
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df.drop(axis=1, columns=['customer_ID','S_2'], inplace=True)\n",
    "test_df.drop(axis=1, columns=['S_2'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "8231acd9-37da-433b-adc4-433c3b1cb1bf",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dropping column D_42\n",
      "Dropping column D_49\n",
      "Dropping column D_66\n",
      "Dropping column D_73\n",
      "Dropping column D_76\n",
      "Dropping column R_9\n",
      "Dropping column B_29\n",
      "Dropping column D_87\n",
      "Dropping column D_88\n",
      "Dropping column D_106\n",
      "Dropping column R_26\n",
      "Dropping column D_108\n",
      "Dropping column D_110\n",
      "Dropping column D_111\n",
      "Dropping column B_39\n",
      "Dropping column B_42\n",
      "Dropping column D_132\n",
      "Dropping column D_134\n",
      "Dropping column D_135\n",
      "Dropping column D_136\n",
      "Dropping column D_137\n",
      "Dropping column D_138\n",
      "Dropping column D_142\n"
     ]
    }
   ],
   "source": [
    "#删除训练集中缺失值数占总数>=75%的字段\n",
    "i=0\n",
    "for col in train_df.columns:\n",
    "    if (train_df[col].isnull().sum()/len(train_df[col])*100) >=75:\n",
    "        print(\"Dropping column\", col)\n",
    "        train_df.drop(labels=col,axis=1,inplace=True)\n",
    "        i=i+1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "47a671ca-1ece-45e3-ba80-ae830f869ae2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dropping column D_42\n",
      "Dropping column D_49\n",
      "Dropping column D_66\n",
      "Dropping column D_73\n",
      "Dropping column D_76\n",
      "Dropping column R_9\n",
      "Dropping column B_29\n",
      "Dropping column D_87\n",
      "Dropping column D_88\n",
      "Dropping column D_106\n",
      "Dropping column R_26\n",
      "Dropping column D_108\n",
      "Dropping column D_110\n",
      "Dropping column D_111\n",
      "Dropping column B_39\n",
      "Dropping column B_42\n",
      "Dropping column D_132\n",
      "Dropping column D_134\n",
      "Dropping column D_135\n",
      "Dropping column D_136\n",
      "Dropping column D_137\n",
      "Dropping column D_138\n",
      "Dropping column D_142\n"
     ]
    }
   ],
   "source": [
    "#删除测试集中缺失值数占总数>=75%的字段\n",
    "i=0\n",
    "for col in test_df.columns:\n",
    "    if (test_df[col].isnull().sum()/len(test_df[col])*100) >=75:\n",
    "        print(\"Dropping column\", col)\n",
    "        test_df.drop(labels=col,axis=1,inplace=True)\n",
    "        i=i+1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "a40f91b9-9334-4cf0-96d8-2dbcbdbbe4cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "#将训练集测试集中的B（余额分类特征）和D（拖欠分类特征）转化为str 类型\n",
    "train_df = train_df.astype({\"B_30\": 'str', \"B_38\": 'str'})\n",
    "test_df = test_df.astype({\"B_30\": 'str', \"B_38\": 'str'})\n",
    "train_df = train_df.astype({\"D_114\": 'str', \"D_116\": 'str', \"D_117\": 'str', \"D_120\": 'str', \"D_126\": 'str', \"D_68\": 'str'})\n",
    "test_df = test_df.astype({\"D_114\": 'str', \"D_116\": 'str', \"D_117\": 'str', \"D_120\": 'str', \"D_126\": 'str', \"D_68\": 'str'})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "ffb177cf-308e-442b-b230-0bb4490cb768",
   "metadata": {},
   "outputs": [],
   "source": [
    "#将训练集中的标签值和特征分开\n",
    "X = train_df.drop(columns='target')\n",
    "y = train_df['target']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "fe676421-77d9-4b00-88ba-ef0ed09ee862",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['D_63',\n",
       " 'D_64',\n",
       " 'D_68',\n",
       " 'B_30',\n",
       " 'B_38',\n",
       " 'D_114',\n",
       " 'D_116',\n",
       " 'D_117',\n",
       " 'D_120',\n",
       " 'D_126']"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#将分类特征和数字特征分别select出来集中处理\n",
    "categorical = list(X.select_dtypes('object').columns)\n",
    "num = list(X.select_dtypes('number').columns)\n",
    "categorical\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "cc2ca42a-b315-4c64-bfd8-ed9c452f795e",
   "metadata": {},
   "outputs": [],
   "source": [
    "#创建分类特征处理pipeline\n",
    "cat_pipe = Pipeline([\n",
    "    ('imputer', SimpleImputer(strategy='most_frequent',missing_values=np.nan)),#strategy='most_frequent'表示对于缺失值（missing_values=np.nan,即 NumPy 中的np.nan表示的缺失值），采用最频繁出现的值进行填充\n",
    "    ('encoder', OneHotEncoder(handle_unknown='ignore')),#handle_unknown='ignore'表示在遇到训练数据中未出现过的类别时，忽略这些未知类别而不产生错误。\n",
    "    ('scaler', StandardScaler(with_mean=False))#StandardScaler类进行标准化处理。它会将数据的特征缩放到均值为 0，标准差为 1 的分布。\n",
    "])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "6ee62271-af48-4827-8790-04fa648e95f8",
   "metadata": {},
   "outputs": [],
   "source": [
    "#创建数值特征处理pipeline\n",
    "num_pipe = Pipeline([\n",
    "    ('imputer', SimpleImputer(strategy='most_frequent', missing_values=np.nan)),\n",
    "    ('scaler', StandardScaler(with_mean=False))\n",
    "])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "de97daab-a0b1-4612-8c78-f11f732046ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将两个管道结合\n",
    "preprocess = ColumnTransformer([\n",
    "    ('cat', cat_pipe, categorical),\n",
    "    ('num', num_pipe, num)\n",
    "])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "7b139d38-7a69-4752-81ca-32b97399b5dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "#将训练集和测试集分割\n",
    "X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.25, stratify=y, random_state=42)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "6be71a94-a8b3-4ca1-8e96-679dfb5ed629",
   "metadata": {},
   "outputs": [],
   "source": [
    "steps = [\n",
    "        ('preprocess', preprocess),#数据预处理\n",
    "        ('over_sampler',SMOTE(random_state = 42)),#过采样步骤：使用SMOTE采样以平衡不平衡数据集。random_state = 42用于设置随机状态，以确保结果的可重复性。\n",
    "        ('under_sampler',RandomUnderSampler()),#欠采样步骤使用RandomUnderSampler进行随机欠采样\n",
    "        ('feature_selection', SelectFromModel(RandomForestClassifier(n_estimators = 10, random_state = 42, n_jobs = -1))),#特征选择步骤结合一个随机森林分类器进行特征选择。这里的随机森林分类器设置了n_estimators = 10表示有 10 个决策树，random_state = 42确保结果可重复，n_jobs = -1表示使用所有可用的处理器核心进行并行计算。\n",
    "        ('dimension_reduction',  LinearDiscriminantAnalysis(n_components=1)),#采用LDA降维random_state = 42确保结果可重复。\n",
    "        ('model_estimator', RandomForestClassifier(random_state = 42))#使用随机森林分类器作为最终的模型估计器。random_state = 42确保结果可重复。\n",
    "    ]\n",
    "pipe = Pipeline(steps, verbose=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "31e24f65-0265-4e64-a846-34507151a4a7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[Pipeline] ........ (step 1 of 6) Processing preprocess, total=   1.7s\n",
      "[Pipeline] ...... (step 2 of 6) Processing over_sampler, total=   0.8s\n",
      "[Pipeline] ..... (step 3 of 6) Processing under_sampler, total=   0.1s\n",
      "[Pipeline] . (step 4 of 6) Processing feature_selection, total=   2.6s\n",
      "[Pipeline]  (step 5 of 6) Processing dimension_reduction, total=   0.5s\n",
      "[Pipeline] ... (step 6 of 6) Processing model_estimator, total=  20.7s\n"
     ]
    }
   ],
   "source": [
    "#set_params(model_estimator=XGBClassifier())\n",
    "pipe_model = pipe.fit(X_train,y_train)\n",
    "pred=pipe_model.predict(X_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "fd319753-af68-4a73-9a9e-0073164953ad",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.7852257625083165\n"
     ]
    }
   ],
   "source": [
    "auc=roc_auc_score(y_test, pred)\n",
    "print(auc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "00800ceb-64a9-4cb8-9104-3912b0580f28",
   "metadata": {},
   "outputs": [],
   "source": [
    "pred_result = pipe_model.predict(test_df)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "6e70e323-d4cb-4e7e-8008-8292876b5bfa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([0, 1, 1, ..., 0, 1, 1])"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pred_result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "4f7125fa-5f71-4842-a878-5a25bcaff5a6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "80000"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_df_new=test_df.reset_index()\n",
    "test_df_new.set_index('customer_ID', inplace=True)\n",
    "y_test_pred = pipe_model.predict(test_df_new)\n",
    "length = len(y_test_pred)\n",
    "length"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "8e661835-e283-49cb-b9a2-dfbe6645ac7b",
   "metadata": {},
   "outputs": [],
   "source": [
    "X_test_predict = test_df_new.groupby('customer_ID').tail(100)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "e82248e9-8293-4282-95b9-f25e08011573",
   "metadata": {},
   "outputs": [],
   "source": [
    "output = pd.DataFrame({'customer_ID': X_test_predict.index,'prediction': y_test_pred})\n",
    "output.to_csv('submission.csv', index=False, header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "23ae5db5-1f48-437f-9e0f-c356741bbea2",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
